﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using Newtonsoft.Json;
using RestSemitaService.Models;

namespace RestSemitaService.Controllers
{
    public class DeliveryTranController : ApiController
    {
        // GET api/feedback
        public IEnumerable<string> Get()
        {
            return new string[] { "value1", "value2" };
        }

        // GET api/feedback/5
        public string Get(string messenger_id)
        {
            try
            {
                SqlConnection thisConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
                thisConnection.Open();
                SqlCommand thisCommand = thisConnection.CreateCommand();
                thisCommand.CommandText = @"             
                select invoice_no, deliver_date, invoice_status, feedback_score, callcenter_id
              from delivery_tran where messenger_id = @messenger_id and invoice_status = 'OnDelivery'
            and deliver_date = convert(varchar, getdate(), 101)";
                thisCommand.Parameters.AddWithValue("@messenger_id", messenger_id);
                using (SqlDataReader reader = thisCommand.ExecuteReader())
                {
                    List<DeliveryTran> DeliveryTran = new List<DeliveryTran>();
                    while (reader.Read())
                    {
                        string _invoice_no = reader["invoice_no"].ToString();
                        string _deliver_date = reader["deliver_date"].ToString();
                        string _invoice_status = reader["invoice_status"].ToString();
                        string _feedback_score = reader["feedback_score"].ToString();
                        string _callcenter_id = reader["callcenter_id"].ToString();
                        DeliveryTran.Add(new DeliveryTran
                        {
                            MessengerId = messenger_id,
                            InvoiceNo = _invoice_no,
                            DeliverDate = _deliver_date,
                            InvoiceStatus = _invoice_status,
                            FeedbackScore = _feedback_score,
                            CallcenterId = _callcenter_id
                        });
                    }
                    thisConnection.Close();
                    string json = JsonConvert.SerializeObject(new
                    {
                        DeliveryTran = DeliveryTran
                    });
                    return json;
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
                return e.StackTrace + " \n >>>>>>>>>>>>>>>>> \n" + e.Message;
            }
        }

		public string Get(string invoice_no, string invoice_status, string address_gps,string messenger_id)
		{
			try
			{
				SqlConnection thisConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
				thisConnection.Open();
				SqlCommand thisCommand = thisConnection.CreateCommand();
				thisCommand.CommandText = @" 
					update	a  
					set		invoice_status = @invoice_status
					from	delivery_tran a 
							inner join (select invoice_no, max(deliver_date) as max_date from delivery_tran group by invoice_no) 
							b on a.invoice_no = b.invoice_no
							and a.deliver_date = b.max_date
					where	a.invoice_no = @invoice_no

					update invoice_header set address_gps = @address_gps, 
                                              invoice_status = @invoice_status 
                    where invoice_no = @invoice_no

					update	messenger_location set last_update_location = @address_gps,last_update_date = CURRENT_TIMESTAMP 
					where	messenger_id = @messenger_id
							and deliver_date = convert(varchar, getdate(), 101)	";

				thisCommand.Parameters.AddWithValue("@invoice_no", invoice_no);
				thisCommand.Parameters.AddWithValue("@invoice_status", invoice_status);
				thisCommand.Parameters.AddWithValue("@address_gps", address_gps);
				thisCommand.Parameters.AddWithValue("@messenger_id", messenger_id);

				if (thisCommand.ExecuteNonQuery() > 0)
				{
					thisConnection.Close();
					return "true";
				}
				else
				{
					thisConnection.Close();
					return "false";
				}
			}
			catch (SqlException e)
			{
				Console.WriteLine(e.Message);
				return e.StackTrace + " \n >>>>>>>>>>>>>>>>> \n" + e.Message;
			}
		}

        // POST api/feedback
        // update status and feed back
        public string Post(string invoice_no, string invoice_status, string feedback_score)
        {
            try
            {
                SqlConnection thisConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
                thisConnection.Open();
                SqlCommand thisCommand = thisConnection.CreateCommand();
                thisCommand.CommandText = @" 
                update a  
                 set invoice_status = @invoice_status , feedback_score = @feedback_score
                 from delivery_tran a 
                 inner join (select invoice_no, max(deliver_date) as max_date from delivery_tran group by invoice_no) 
		         b on   a.invoice_no = b.invoice_no
		         and a.deliver_date = b.max_date
	             where  a.invoice_no = @invoice_no";
                thisCommand.Parameters.AddWithValue("@invoice_no", invoice_no);
                thisCommand.Parameters.AddWithValue("@invoice_status", invoice_status);
                thisCommand.Parameters.AddWithValue("@feedback_score", feedback_score);
                if (thisCommand.ExecuteNonQuery() > 0)
                {
                    thisConnection.Close();
                    return "true";
                }
                else
                {
                    thisConnection.Close();
                    return "false";
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
                return e.StackTrace + " \n >>>>>>>>>>>>>>>>> \n" + e.Message;
            }
        }

        // PUT api/feedback/5
        public void Put(int id, [FromBody]string value)
        {
        }

        // DELETE api/feedback/5
        public void Delete(int id)
        {
        }
    }
}
